package com.lb.jwitter.connectDatabase;

import java.sql.*;
import com.dashboard.taichinh.ThongKe;

public class ConnectBKPay {
	private  String connectionString;
	private  String userName;
	private  String password;
	private  Connection connection;
	private  Statement st;
	private String jsonData2;
	public  Connection getConnection(){
		userName="bkpay";
		password="123456";
		String databaseName="bk_pay_3";
		connection=null;
		connectionString="jdbc:mysql://172.28.2.32:3306/"+databaseName+"?characterEncoding=utf-8";
		try{
			Class.forName("com.mysql.jdbc.Driver");
			connection=DriverManager.getConnection(connectionString, userName, password);
			st=connection.createStatement();
			
		}
		catch(Exception e){
			System.out.println(e);
		}
		return connection;
	}
	
	//Tra ve chuoi json mo ta thong tin tinh toan tu 4 hoc ky lien tiep
	public String getJSonData2(){
		
		return jsonData2;
	}
	
	//Thong ke thong tin nguon thu, thuc thu, da thu, con lai, so nguoi nop, so nguoi chua nop, so nguoi nop tre...
	public String getJSonDataThongKe(String query,String semester){
		String jsonData="";
		ResultSet data=getData(query);
		ThongKe thongKe=new ThongKe();
		ThongKe thongKe2=new ThongKe();
		
		if(data!=null){
			jsonData="[";
			try {
				
				while (data.next()){
					String sem=data.getString("nhhk_thangnam");
					int thanhToanId=data.getInt("loai_thanh_toan_id");
					
					long canThu=data.getLong("canthu");
					long tamThu=data.getLong("tamthu");
					long thucThu=data.getLong("thucthu");
					long conLai=data.getLong("conlai");
					int tongNguoi=data.getInt("tongnguoi");
					int soNguoiConLai=data.getInt("sosvchuadong");
					
					//tinh toan cho hoc ky hien tai
					if(sem.equals(semester)){
						
						thongKe.setStatistic(thanhToanId, canThu, tamThu, thucThu, conLai, tongNguoi, soNguoiConLai);
						thongKe2.setStatistic(thanhToanId, canThu, tamThu, thucThu, conLai, tongNguoi, soNguoiConLai);
					}
					
					//tinh toan cho 4 hoc ky lien tiep
					else{
						thongKe2.setStatistic(thanhToanId, canThu, tamThu, thucThu, conLai, tongNguoi, soNguoiConLai);
					}
					
					
				}
				//tinh so nguoi nop tre
				query="select C.loai_thanh_toan_id,count(C.loai_thanh_toan_id) as count"
						+" from khoan_thanh_toan D,"
						+" (select A.loai_thanh_toan_id, B. muc_thanh_toan_id"
						+" from muc_thanh_toan B,"
						+" (select distinct loai_thanh_toan_id,dot_thanh_toan_id from dot_thanh_toan where nhhk_thangnam='"+semester+"' and thu_chi='IN') A"
						+" where A.dot_thanh_toan_id=B.dot_thanh_toan_id and B.da_huy!=1) C"
						+" where D.muc_thanh_toan_id=C.muc_thanh_toan_id and ngay_thuc_thu_chi>ngay_du_kien_het_han"
						+" group by C.loai_thanh_toan_id";
				
				data=getData(query);
				if(data!=null){
					while(data.next()){
						int id=data.getInt("loai_thanh_toan_id");
						int count=data.getInt("count");
						
						thongKe.setSoSVNopTre(id, count);
					}
				}
				
				//Dinh dang json cho hoc ky hien tai
				jsonData=jsonData+"{\"kind\":\"QT\",\"canThu\":"+thongKe.getCanThuQT()+",\"tamThu\":"+thongKe.getTamThuQT()
						+",\"thucThu\":"+thongKe.getThucThuQT()+",\"conLai\":"+thongKe.getConLaiQT()+",\"tongNguoi\":"
						+thongKe.getSoSVCanNopQT()+",\"soNguoiDaNop\":"+(thongKe.getSoSVCanNopQT()-thongKe.getSoSVChuaNopQT())
						+",\"soNguoiChuaNop\":"+thongKe.getSoSVChuaNopQT()+",\"soNguoiNopTre\":"+thongKe.getSoSVNopTreQT()+"},";
				
				jsonData=jsonData+"{\"kind\":\"DHCQ\",\"canThu\":"+thongKe.getCanThuDHCQ()+",\"tamThu\":"+thongKe.getTamThuDHCQ()
						+",\"thucThu\":"+thongKe.getThucThuDHCQ()+",\"conLai\":"+thongKe.getConLaiDHCQ()+",\"tongNguoi\":"
						+thongKe.getSoSVCanNopDHCQ()+",\"soNguoiDaNop\":"+(thongKe.getSoSVCanNopDHCQ()-thongKe.getSoSVChuaNopDHCQ())
						+",\"soNguoiChuaNop\":"+thongKe.getSoSVChuaNopDHCQ()+",\"soNguoiNopTre\":"+thongKe.getSoSVNopTreDHCQ()+"},";
				jsonData=jsonData+"{\"kind\":\"KCQ\",\"canThu\":"+thongKe.getCanThuKCQ()+",\"tamThu\":"+thongKe.getTamThuKCQ()
						+",\"thucThu\":"+thongKe.getThucThuKCQ()+",\"conLai\":"+thongKe.getConLaiKCQ()+",\"tongNguoi\":"
						+thongKe.getSoSVCanNopKCQ()+",\"soNguoiDaNop\":"+(thongKe.getSoSVCanNopKCQ()-thongKe.getSoSVChuaNopKCQ())
						+",\"soNguoiChuaNop\":"+thongKe.getSoSVChuaNopKCQ()+",\"soNguoiNopTre\":"+thongKe.getSoSVNopTreKCQ()+"},";
				
				jsonData=jsonData+"{\"kind\":\"SDH\",\"canThu\":"+thongKe.getCanThuSDH()+",\"tamThu\":"+thongKe.getTamThuSDH()
						+",\"thucThu\":"+thongKe.getThucThuSDH()+",\"conLai\":"+thongKe.getConLaiSDH()+",\"tongNguoi\":"
						+thongKe.getSoSVCanNopSDH()+",\"soNguoiDaNop\":"+(thongKe.getSoSVCanNopSDH()-thongKe.getSoSVChuaNopSDH())
						+",\"soNguoiChuaNop\":"+thongKe.getSoSVChuaNopSDH()+",\"soNguoiNopTre\":"+thongKe.getSoSVNopTreSDH()+"},";
				jsonData=jsonData+"{\"kind\":\"DTSDH\",\"canThu\":"+thongKe.getCanThuDTSDH()+",\"tamThu\":"+thongKe.getTamThuDTSDH()
						+",\"thucThu\":"+thongKe.getThucThuDTSDH()+",\"conLai\":"+thongKe.getConLaiDTSDH()+",\"tongNguoi\":"
						+thongKe.getSoSVCanNopDTSDH()+",\"soNguoiDaNop\":"+(thongKe.getSoSVCanNopDTSDH()-thongKe.getSoSVChuaNopDTSDH())
						+",\"soNguoiChuaNop\":"+thongKe.getSoSVChuaNopDTSDH()+",\"soNguoiNopTre\":"+thongKe.getSoSVNopTreDTSDH()+"},";
				
				jsonData=jsonData+"{\"kind\":\"Other\",\"canThu\":"+thongKe.getCanThuOther()+",\"tamThu\":"+thongKe.getTamThuOther()
						+",\"thucThu\":"+thongKe.getThucThuOther()+",\"conLai\":"+thongKe.getConLaiOther()+",\"tongNguoi\":"
						+thongKe.getSoSVCanNopOther()+",\"soNguoiDaNop\":"+(thongKe.getSoSVCanNopOther()-thongKe.getSoSVChuaNopOther())
						+",\"soNguoiChuaNop\":"+thongKe.getSoSVChuaNopOther()+",\"soNguoiNopTre\":"+thongKe.getSoSVNopTreOther()+"}";
				
				jsonData=jsonData+"]";
				
				//////////////////////////////////////////////////////////////////////////////////////////////
				//dinh dang json cho 4 hoc ky lien tiep
				jsonData2="[";
				jsonData2=jsonData2+"{\"kind\":\"QT\",\"canThu\":"+thongKe2.getCanThuQT()+",\"tamThu\":"+thongKe2.getTamThuQT()
						+",\"thucThu\":"+thongKe2.getThucThuQT()+",\"conLai\":"+thongKe2.getConLaiQT()+",\"tongNguoi\":"
						+thongKe2.getSoSVCanNopQT()+",\"soNguoiDaNop\":"+(thongKe2.getSoSVCanNopQT()-thongKe2.getSoSVChuaNopQT())
						+",\"soNguoiChuaNop\":"+thongKe2.getSoSVChuaNopQT()+"},";
				
				jsonData2=jsonData2+"{\"kind\":\"DHCQ\",\"canThu\":"+thongKe2.getCanThuDHCQ()+",\"tamThu\":"+thongKe2.getTamThuDHCQ()
						+",\"thucThu\":"+thongKe2.getThucThuDHCQ()+",\"conLai\":"+thongKe2.getConLaiDHCQ()+",\"tongNguoi\":"
						+thongKe2.getSoSVCanNopDHCQ()+",\"soNguoiDaNop\":"+(thongKe2.getSoSVCanNopDHCQ()-thongKe2.getSoSVChuaNopDHCQ())
						+",\"soNguoiChuaNop\":"+thongKe2.getSoSVChuaNopDHCQ()+"},";
				jsonData2=jsonData2+"{\"kind\":\"KCQ\",\"canThu\":"+thongKe2.getCanThuKCQ()+",\"tamThu\":"+thongKe2.getTamThuKCQ()
						+",\"thucThu\":"+thongKe2.getThucThuKCQ()+",\"conLai\":"+thongKe2.getConLaiKCQ()+",\"tongNguoi\":"
						+thongKe2.getSoSVCanNopKCQ()+",\"soNguoiDaNop\":"+(thongKe2.getSoSVCanNopKCQ()-thongKe2.getSoSVChuaNopKCQ())
						+",\"soNguoiChuaNop\":"+thongKe2.getSoSVChuaNopKCQ()+"},";
				
				jsonData2=jsonData2+"{\"kind\":\"SDH\",\"canThu\":"+thongKe2.getCanThuSDH()+",\"tamThu\":"+thongKe2.getTamThuSDH()
						+",\"thucThu\":"+thongKe2.getThucThuSDH()+",\"conLai\":"+thongKe2.getConLaiSDH()+",\"tongNguoi\":"
						+thongKe2.getSoSVCanNopSDH()+",\"soNguoiDaNop\":"+(thongKe2.getSoSVCanNopSDH()-thongKe2.getSoSVChuaNopSDH())
						+",\"soNguoiChuaNop\":"+thongKe2.getSoSVChuaNopSDH()+"},";
				jsonData2=jsonData2+"{\"kind\":\"DTSDH\",\"canThu\":"+thongKe2.getCanThuDTSDH()+",\"tamThu\":"+thongKe2.getTamThuDTSDH()
						+",\"thucThu\":"+thongKe2.getThucThuDTSDH()+",\"conLai\":"+thongKe2.getConLaiDTSDH()+",\"tongNguoi\":"
						+thongKe2.getSoSVCanNopDTSDH()+",\"soNguoiDaNop\":"+(thongKe2.getSoSVCanNopDTSDH()-thongKe2.getSoSVChuaNopDTSDH())
						+",\"soNguoiChuaNop\":"+thongKe2.getSoSVChuaNopDTSDH()+"},";
				
				jsonData2=jsonData2+"{\"kind\":\"Other\",\"canThu\":"+thongKe2.getCanThuOther()+",\"tamThu\":"+thongKe2.getTamThuOther()
						+",\"thucThu\":"+thongKe2.getThucThuOther()+",\"conLai\":"+thongKe2.getConLaiOther()+",\"tongNguoi\":"
						+thongKe2.getSoSVCanNopOther()+",\"soNguoiDaNop\":"+(thongKe2.getSoSVCanNopOther()-thongKe2.getSoSVChuaNopOther())
						+",\"soNguoiChuaNop\":"+thongKe2.getSoSVChuaNopOther()+"}";
				
				jsonData2=jsonData2+"]";
				
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
		}
		//return jsonData;
		return "{\"d1\":"+jsonData+",\"d2\":"+jsonData2+"}";
	}
	
	//Lay thong tin co cau loai hinh dao tao, tra ve dinh dang json
	public String getJsonDataLoaiHinhDaoTao(String query){
		String jsonData="";
		ResultSet data=getData(query);
		if(data!=null){
			jsonData="[";
			try {
				
				while (data.next()){
					int canthu=data.getInt("canthu");
					String maHeDaoTao=data.getString("mahedaotao");
					jsonData=jsonData+"{\"HeDaoTao\":\""+maHeDaoTao+"\",\"TongThu\":"+canthu+"},";
				}
				jsonData=jsonData+"]";
				jsonData=jsonData.replaceAll(",]", "]");
			}
			catch(Exception e){
				
			}
		}
		return jsonData;
	}
	
	public  ResultSet getData(String query){
		ResultSet data=null;
		try {
			data=st.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return data;
	}
	
	public void closeConnection(){
		
		if(connection!=null){
			try {
				st.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	
}
